﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Models;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.IO;

namespace InvExport
{
    public partial class FrmMain : Form
    {
        public FrmMain()
        {
            InitializeComponent();
        }

        private KPPathModel _path = null;
        private string _sDate;
        private string _eDate;
        private string _containCancel = " and t1.ZFBZ=0";
        private List<DataGroupModel> _groupList = new List<DataGroupModel>();
        private Dictionary<string, List<DataModel>> _dataList = new Dictionary<string, List<DataModel>>();
        private List<DataModel> _data = new List<DataModel>();
        private string _expPath;

        #region 窗口展现

        private void Form1_Shown(object sender, EventArgs e)
        {
            tbxSDate.Value = tbxEDate.Value = DateTime.Now;
            picGroup.Dock = DockStyle.Fill;
            bsDataGroup.DataSource = _groupList;
            bsDataShow.DataSource = _data;
            getKPList();
        }

        #endregion 窗口展现

        #region 读取开票系统数据库列表

        private void getKPList()
        {
            try
            {
                lblSelect.Text = "当前选择：无";
                var l = AisinoManage.GetKPList();
                cbxTaxPath.DisplayMember = "Name";
                cbxTaxPath.DataSource = l;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion 读取开票系统数据库列表

        #region 选择开票路径

        private void cbxTaxPath_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                lblSelect.Text = "当前选择：无";
                lblTaxCode.Text = "当前税号：";
                lblPath.Text = "数据路径：";
                lblMachine.Text = "开票机号：";
                _path = null;
                if (null == cbxTaxPath.SelectedValue)
                    return;
                _path = cbxTaxPath.SelectedValue as KPPathModel;
                if (null == _path)
                    return;
                lblSelect.Text = "当前选择：" + _path.Name;
                lblTaxCode.Text = "当前税号：" + _path.TaxCode;
                lblMachine.Text = "开票机号：" + _path.MachineNum;
                lblPath.Text = "数据路径：" + _path.DBPath;
            }
            catch (Exception)
            {
            }
        }

        #endregion 选择开票路径

        #region 读取分组

        private void getDataGroup()
        {
            try
            {
                _groupList.Clear();
                _dataList.Clear();
                _data.Clear();
                Invoke(new MethodInvoker(() =>
                {
                    picGroup.Visible = true;
                    dgvGroup.RefreshData();
                    dgvData.RefreshData();
                }));
                string sqlstr = $"select Product,TaxRate,Price from (select t1.FPDM InvCode,t1.FPHM InvNum,t1.GFMC CustName,t1.GFSH CustTax,t1.GFDZDH CustAddr,t1.GFYHZH CustBank,t1.KPRQ InvDate,t.SPMC Product,t.SL Quantity,case t.HSJBZ when 0 then (t.JE + t.SE)/t.SL else t.DJ end Price,t.JE Amount,t.SLV TaxRate,t.SE TaxAmount,t1.ZFBZ State from XXFP_MX t left join XXFP t1 on t.FPDM=t1.FPDM and t.FPHM=t1.FPHM where t1.KPRQ>='{_sDate}' and t1.KPRQ<='{_eDate}'{_containCancel}) v1 group by v1.Product,v1.TaxRate,v1.Price";
                var dt = DBUtil.Query(sqlstr);
                if (dt.Rows.Count > 0)
                {
                    List<DataGroupModel> l = new List<DataGroupModel>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        var dr = dt.Rows[i];
                        DataGroupModel dgm = new DataGroupModel
                        {
                            Price = dr.IsNull("Price") ? 0 : Convert.ToDecimal(dr["Price"]),
                            ProName = dr.IsNull("Product") ? "" : dr["Product"].ToString(),
                            TaxRate = dr.IsNull("TaxRate") ? 0 : Convert.ToDecimal(dr["TaxRate"])
                        };
                        Regex r = new Regex(@"\*.*\*");
                        dgm.ProName = r.Replace(dgm.ProName, "");
                        l.Add(dgm);
                    }
                    l.Add(new DataGroupModel { Price = 0, ProName = "全部", TaxRate = 0 });
                    if (l.Count > 0)
                    {
                        getEntity(l);
                        _groupList.AddRange(l);
                    }
                }
                Invoke(new MethodInvoker(() =>
                {
                    btnQuery.Enabled = true;
                    btnExport.Enabled = true;
                    dgvGroup.RefreshData();
                    picGroup.Visible = false;
                }));
            }
            catch (Exception ex)
            {
                Invoke(new MethodInvoker(() =>
                {
                    btnQuery.Enabled = true;
                    btnExport.Enabled = true;
                    picGroup.Visible = false;
                    MessageBox.Show(ex.Message);
                }));
            }
        }

        #endregion 读取分组

        #region 查询按钮

        private void btnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                if (tbxSDate.Value > tbxEDate.Value)
                    throw new Exception("起始时间不能大于结束时间");
                _sDate = tbxSDate.Value.ToString("yyyy-MM-dd 00:00:00");
                _eDate = tbxEDate.Value.ToString("yyyy-MM-dd 23:59:59");
                if (chkCancel.Checked)
                    _containCancel = "";
                else
                    _containCancel = " and t1.ZFBZ=0";
                KPPathModel kpm = cbxTaxPath.SelectedValue as KPPathModel;
                if (null == kpm)
                    throw new Exception("请先选择税号");
                if (DBUtil.GetCurrentPath() != kpm.DBPath + @"Bin\cc3268mt.dll")
                {
                    DBUtil.DBPath = kpm.DBPath + @"Bin\cc3268mt.dll";
                    DBUtil.CloseDB();
                }
                btnQuery.Enabled = false;
                btnExport.Enabled = false;
                Task tsk = new Task(getDataGroup);
                tsk.Start();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion 查询按钮

        #region 查询明细数据

        private void getEntity(List<DataGroupModel> group)
        {
            try
            {
                string sqlstr = $"select t1.FPDM InvCode,t1.FPHM InvNum,t1.GFMC CustName,t1.GFSH CustTax,t1.GFDZDH CustAddr,t1.GFYHZH CustBank,t1.KPRQ InvDate,t.SPMC Product,t.SL Quantity,case t.HSJBZ when 0 then (t.JE + t.SE)/t.SL else t.DJ end Price,t.JE Amount,t.SLV TaxRate,t.SE TaxAmount,t1.ZFBZ State,t.FPZL InvType from XXFP_MX t left join XXFP t1 on t.FPDM=t1.FPDM and t.FPHM=t1.FPHM where t1.KPRQ>='{_sDate}' and t1.KPRQ<='{_eDate}'{_containCancel}";
                var dt = DBUtil.Query(sqlstr);
                List<DataModel> l = new List<DataModel>();
                //清空分组数据字典
                _dataList.Clear();
                //所有数据写list
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        var dr = dt.Rows[i];
                        DataModel dm = new DataModel
                        {
                            Amount = dr.IsNull("Amount") ? 0 : Convert.ToDecimal(dr["Amount"]),
                            CustAddr = dr.IsNull("CustAddr") ? "" : dr["CustAddr"].ToString(),
                            CustBank = dr.IsNull("CustBank") ? "" : dr["CustBank"].ToString(),
                            CustName = dr.IsNull("CustName") ? "" : dr["CustName"].ToString(),
                            CustTax = dr.IsNull("CustTax") ? "" : dr["CustTax"].ToString(),
                            InvCode = dr.IsNull("InvCode") ? "" : dr["InvCode"].ToString(),
                            InvDate = dr.IsNull("InvDate") ? "" : dr["InvDate"].ToString(),
                            InvNum = dr.IsNull("InvNum") ? "" : dr["InvNum"].ToString(),
                            Price = dr.IsNull("Price") ? 0 : Convert.ToDecimal(dr["Price"]),
                            Product = dr.IsNull("Product") ? "" : dr["Product"].ToString(),
                            Quantity = dr.IsNull("Quantity") ? 0 : Convert.ToDecimal(dr["Quantity"]),
                            TaxAmount = dr.IsNull("TaxAmount") ? 0 : Convert.ToDecimal(dr["TaxAmount"]),
                            TaxRate = dr.IsNull("TaxRate") ? 0 : Convert.ToDecimal(dr["TaxRate"]),
                            InvState = dr.IsNull("State") ? 1 : Convert.ToInt32(dr["State"]),
                            InvType = dr.IsNull("InvType") ? "c" : dr["InvType"].ToString().Trim()
                        };
                        Regex r = new Regex(@"\*.*\*");
                        dm.Product = r.Replace(dm.Product, "");
                        l.Add(dm);
                    }
                }
                if (l.Count > 0)
                {
                    //分组明细写到分组字典中
                    for (int i = 0; i < group.Count; i++)
                    {
                        List<DataModel> tmp = new List<DataModel>();
                        if (group[i].ProName == "全部")
                        {
                            tmp.AddRange(l);
                        }
                        else
                        {
                            var res = l.Where(p => p.Product == group[i].ProName && p.TaxRate == group[i].TaxRate && p.Price == group[i].Price);
                            if (res.Any())
                            {
                                tmp.AddRange(res.ToList());
                            }
                        }
                        if (tmp.Count > 0)
                        {
                            decimal totalQuantity = tmp.Sum(p => p.Quantity);
                            decimal totalAmount = tmp.Sum(p => p.Amount);
                            decimal totalTaxAmount = tmp.Sum(p => p.TaxAmount);
                            DataModel dm1 = new DataModel
                            {
                                InvCode = "合计",
                                Quantity = totalQuantity,
                                Amount = totalAmount,
                                TaxAmount = totalTaxAmount
                            };
                            tmp.Add(dm1);
                            _dataList[group[i].ProName + "_" + group[i].TaxRate.ToString() + "_" + group[i].Price] = tmp;
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion 查询明细数据

        #region 分组表格焦点行改变

        private void dgvGroup_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            try
            {
                if (e.FocusedRowHandle >= 0)
                {
                    DataGroupModel dm = dgvGroup.GetRow(e.FocusedRowHandle) as DataGroupModel;
                    if (null == dm)
                        return;
                    _data.Clear();
                    var l = _dataList[dm.ProName + "_" + dm.TaxRate.ToString() + "_" + dm.Price.ToString()];
                    _data.AddRange(l);
                    dgvData.BestFitColumns();
                    dgvData.RefreshData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion 分组表格焦点行改变

        #region 导出EXCEL

        private void exportExcel()
        {
            try
            {
                Invoke(new MethodInvoker(() =>
                {
                    btnQuery.Enabled = false;
                    btnExport.Enabled = false;
                    picExport.Visible = true;
                    gridGroup.Enabled = false;
                    gridData.Enabled = false;
                }));

                foreach (var lst in _dataList)
                {
                    int curRow = 0;
                    string fileName = _expPath + lst.Key + ".xls";
                    IWorkbook book = new HSSFWorkbook();
                    ISheet sheet = book.CreateSheet("发票明细");

                    #region 数据行样式

                    ICellStyle norm1 = book.CreateCellStyle();
                    IFont normFont = book.CreateFont();
                    normFont.FontName = "宋体";
                    normFont.Color = HSSFColor.Black.Index;
                    normFont.FontHeightInPoints = 12;
                    norm1.SetFont(normFont);
                    norm1.FillForegroundColor = HSSFColor.White.Index;
                    norm1.FillPattern = FillPattern.SolidForeground;

                    ICellStyle norm2 = book.CreateCellStyle();
                    norm2.SetFont(normFont);
                    norm2.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index;
                    norm2.FillPattern = FillPattern.SolidForeground;

                    #endregion 数据行样式

                    #region 标题样式

                    ICellStyle title = book.CreateCellStyle();
                    IFont titleFont = book.CreateFont();
                    titleFont.FontName = "黑体";
                    titleFont.Color = HSSFColor.Black.Index;
                    titleFont.FontHeightInPoints = 14;
                    title.SetFont(titleFont);
                    title.FillForegroundColor = HSSFColor.Grey25Percent.Index;
                    title.FillPattern = FillPattern.SolidForeground;
                    title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                    #endregion 标题样式

                    #region 汇总行样式

                    ICellStyle coll = book.CreateCellStyle();
                    IFont collFont = book.CreateFont();
                    collFont.FontName = "宋体";
                    collFont.Color = HSSFColor.Black.Index;
                    collFont.FontHeightInPoints = 12;
                    coll.SetFont(collFont);
                    coll.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                    coll.FillForegroundColor = HSSFColor.Tan.Index;
                    coll.FillPattern = FillPattern.SolidForeground;

                    #endregion 汇总行样式

                    #region 标题

                    IRow row = sheet.CreateRow(curRow);
                    ICell cell = row.CreateCell(0, CellType.String);
                    cell.SetCellValue("发票代码");
                    cell = row.CreateCell(1, CellType.String);
                    cell.SetCellValue("发票号码");
                    cell = row.CreateCell(2, CellType.String);
                    cell.SetCellValue("客户名");
                    cell = row.CreateCell(3, CellType.String);
                    cell.SetCellValue("税号");
                    cell = row.CreateCell(4, CellType.String);
                    cell.SetCellValue("地址电话");
                    cell = row.CreateCell(5, CellType.String);
                    cell.SetCellValue("银行账号");
                    cell = row.CreateCell(6, CellType.String);
                    cell.SetCellValue("开票时间");
                    cell = row.CreateCell(7, CellType.String);
                    cell.SetCellValue("商品名");
                    cell = row.CreateCell(8, CellType.String);
                    cell.SetCellValue("数量");
                    cell = row.CreateCell(9, CellType.String);
                    cell.SetCellValue("单价");
                    cell = row.CreateCell(10, CellType.String);
                    cell.SetCellValue("不含税金额");
                    cell = row.CreateCell(11, CellType.String);
                    cell.SetCellValue("税额");
                    cell = row.CreateCell(12, CellType.String);
                    cell.SetCellValue("税率");
                    cell = row.CreateCell(13, CellType.String);
                    cell.SetCellValue("价税合计");
                    cell = row.CreateCell(14, CellType.String);
                    cell.SetCellValue("发票状态");
                    cell = row.CreateCell(15, CellType.String);
                    cell.SetCellValue("发票类型");
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        row.GetCell(i).CellStyle = title;
                    }
                    curRow++;

                    #endregion 标题

                    #region 数据

                    List<DataModel> dat = lst.Value;
                    for (int i = 0; i < dat.Count; i++)
                    {
                        row = sheet.CreateRow(curRow);
                        cell = row.CreateCell(0, CellType.String);
                        cell.SetCellValue(dat[i].InvCode);
                        cell = row.CreateCell(1, CellType.String);
                        cell.SetCellValue(dat[i].InvNum);
                        cell = row.CreateCell(2, CellType.String);
                        cell.SetCellValue(dat[i].CustName);
                        cell = row.CreateCell(3, CellType.String);
                        cell.SetCellValue(dat[i].CustTax);
                        cell = row.CreateCell(4, CellType.String);
                        cell.SetCellValue(dat[i].CustAddr);
                        cell = row.CreateCell(5, CellType.String);
                        cell.SetCellValue(dat[i].CustBank);
                        cell = row.CreateCell(6, CellType.String);
                        cell.SetCellValue(dat[i].InvDate);
                        cell = row.CreateCell(7, CellType.String);
                        cell.SetCellValue(dat[i].Product);
                        cell = row.CreateCell(8, CellType.String);
                        cell.SetCellValue(dat[i].Quantity.ToString());
                        cell = row.CreateCell(9, CellType.String);
                        cell.SetCellValue(dat[i].InvCode == "合计" ? "" : dat[i].Price.ToString());
                        cell = row.CreateCell(10, CellType.String);
                        cell.SetCellValue(dat[i].Amount.ToString());
                        cell = row.CreateCell(11, CellType.String);
                        cell.SetCellValue(dat[i].TaxAmount.ToString());
                        cell = row.CreateCell(12, CellType.String);
                        cell.SetCellValue(dat[i].InvCode == "合计" ? "" : dat[i].TaxRate.ToString());
                        cell = row.CreateCell(13, CellType.String);
                        cell.SetCellValue(dat[i].TotalAmount.ToString());
                        cell = row.CreateCell(14, CellType.String);
                        cell.SetCellValue(dat[i].InvCode == "合计" ? "" : dat[i].InvStateString);
                        cell = row.CreateCell(15, CellType.String);
                        cell.SetCellValue(dat[i].InvCode == "合计" ? "" : dat[i].InvTypeString);
                        for (int i2 = 0; i2 < row.Cells.Count; i2++)
                        {
                            if (dat[i].InvCode == "合计")
                            {
                                row.GetCell(i2).CellStyle = coll;
                            }
                            else
                            {
                                if (curRow % 2 > 0)
                                    row.GetCell(i2).CellStyle = norm2;
                                else
                                    row.GetCell(i2).CellStyle = norm1;
                            }
                        }
                        curRow++;
                    }

                    #endregion 数据

                    //自动调整大小
                    for (int j = 0; j < 16; j++)
                    {
                        sheet.AutoSizeColumn(j);
                    }
                    //删除已存在的文件
                    if (File.Exists(fileName))
                        File.Delete(fileName);
                    //保存文件
                    FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    book.Write(fs);
                    fs.Flush();
                    fs.Close();
                }
                Invoke(new MethodInvoker(() =>
                {
                    btnQuery.Enabled = true;
                    btnExport.Enabled = true;
                    picExport.Visible = false;
                    gridGroup.Enabled = true;
                    gridData.Enabled = true;
                    MessageBox.Show("导出完成！");
                }));
            }
            catch (Exception ex)
            {
                Invoke(new MethodInvoker(() =>
                {
                    btnQuery.Enabled = true;
                    btnExport.Enabled = true;
                    gridGroup.Enabled = true;
                    gridData.Enabled = true;
                    picExport.Visible = false;
                    MessageBox.Show(ex.Message);
                }));
            }
        }

        #endregion 导出EXCEL

        #region 合计行颜色

        private void dgvData_RowCellStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowCellStyleEventArgs e)
        {
            try
            {
                var row = dgvData.GetRow(e.RowHandle) as DataModel;
                if (null == row)
                    return;
                if (row.InvCode == "合计")
                {
                    e.Appearance.BackColor = Color.AntiqueWhite;
                }
            }
            catch (Exception)
            {
            }
        }

        #endregion 合计行颜色

        #region 合计行的单价/税率不显示0

        private void dgvData_CustomColumnDisplayText(object sender, DevExpress.XtraGrid.Views.Base.CustomColumnDisplayTextEventArgs e)
        {
            try
            {
                var row = _data[e.ListSourceRowIndex];
                if (row.InvCode == "合计")
                {
                    if (e.Column.FieldName.Contains("InvType") || e.Column.FieldName.Contains("Price") || e.Column.FieldName.Contains("TaxRate") || e.Column.FieldName.Contains("InvState"))
                    {
                        e.DisplayText = "";
                    }
                }
            }
            catch (Exception)
            {
            }
        }

        #endregion 合计行的单价/税率不显示0

        #region 导出按钮

        private void btnExport_Click_1(object sender, EventArgs e)
        {
            try
            {
                if (_dataList.Count < 1)
                    throw new Exception("没有需要导出的数据！");
                _expPath = "";
                var dlgRst = dlgFolder.ShowDialog();
                if (dlgRst == DialogResult.Cancel)
                    return;
                _expPath = dlgFolder.SelectedPath;
                if (_expPath.Substring(_expPath.Length - 1) != "\\")
                    _expPath += "\\";
                Task tsk = new Task(exportExcel);
                tsk.Start();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion 导出按钮
    }
}